"""
CREATE TABLE housing_data (
    id SERIAL PRIMARY KEY,
    region VARCHAR(50),
    business_district VARCHAR(50),
    community VARCHAR(100),
    listing_year INTEGER,
    listing_month NUMERIC(6, 2),  -- <--- 已从 NUMERIC(4, 2) 修改为 NUMERIC(6, 2)
    unit_price_avg NUMERIC,
    unit_price_median NUMERIC,
    unit_price_min NUMERIC,
    unit_price_max NUMERIC,
    total_price_avg NUMERIC,
    total_price_median NUMERIC,
    total_price_min NUMERIC,
    total_price_max NUMERIC,
    longitude NUMERIC(11, 8),
    latitude NUMERIC(10, 8)
)
"""
"""
CREATE TABLE footprints (
    user_id INTEGER NOT NULL,
    property_id INTEGER NOT NULL,
    notes TEXT,
    rating INTEGER,
    added_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- 定义复合主键，确保一个用户对一个小区只能有一条足迹记录
    PRIMARY KEY (user_id, property_id),
    
    -- 定义外键，并设置级联删除，以保持数据一致性
    CONSTRAINT fk_user
        FOREIGN KEY(user_id) 
        REFERENCES users(id) 
        ON DELETE CASCADE,
        
    CONSTRAINT fk_property
        FOREIGN KEY(property_id) 
        REFERENCES properties(id) 
        ON DELETE CASCADE
);
"""

"""
ALTER TABLE housing_data RENAME TO properties;"""

"""

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password TEXT NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user',
    apikey VARCHAR(36) UNIQUE,
    quota INTEGER DEFAULT 60
);
"""

"""
   -- 将 listing_month 的值转换为 NUMERIC(5, 2)
UPDATE properties
SET listing_month = CAST(CAST(listing_month * 100 AS INTEGER) % 100 AS NUMERIC(5,2))
WHERE listing_month >= 1000;
"""

"""
SELECT setval(pg_get_serial_sequence('properties', 'id'), COALESCE((SELECT MAX(id) FROM properties), 1));
"""

"""
psql -U postgres -d summer_2025
\copy housing_data FROM 'wuhan_price.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');
"""

